IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('MD3.CreatePrimaryKey'))
BEGIN
	DROP PROCEDURE MD3.CreatePrimaryKey
END
GO

CREATE  Procedure MD3.CreatePrimaryKey   
( 
     @SchemaName		SYSNAME   
    ,@TableName	    	SYSNAME    
    ,@PrimaryKeyName	SYSNAME
    ,@KeyColumns        VARCHAR(MAX)    -- comma-separated list of key columns; append (-) to column name for DESC
    ,@IsClustered       BIT = 1			-- default is Clustered (1)
    ,@FillFactor        TINYINT = 100	-- default is 100  
    ,@PadIndex          BIT = 0		    -- default is OFF (0)
    ,@FileGroupOrPartition         VARCHAR(MAX)= NULL  -- default is PRIMARY
	,@DropPK			BIT = 0
	,@DataCompressionType VARCHAR(4) = 'ROW'  -- ROW/PAGE/NULL/NONE 

  )  
  
AS    
BEGIN

	SET NOCOUNT ON  

	DECLARE @CurrentPKName SYSNAME
		   ,@Msg VARCHAR(MAX)
		   ,@SQL VARCHAR(MAX);
	  
	SELECT  @IsClustered     = ISNULL(@IsClustered, 1)
			,@FillFactor      = ISNULL(@FillFactor, 100)
			,@PadIndex		  = ISNULL(@PadIndex,0)


		----------------------------------------------------------------------------------
		-- Normalize Parameters.
		----------------------------------------------------------------------------------
		SELECT @SchemaName = MD3.f_RemoveBrackets (@SchemaName);
		SELECT @TableName = MD3.f_RemoveBrackets (@TableName);
		SELECT @PrimaryKeyName = MD3.f_RemoveBrackets (@PrimaryKeyName);
		SELECT @FileGroupOrPartition = MD3.f_RemoveBrackets (@FileGroupOrPartition);
		SELECT @KeyColumns = MD3.f_RemoveBrackets (@KeyColumns);
		--Set the compression option
		SELECT @DataCompressionType = COALESCE(@DataCompressionType, 'NONE') ;
		SELECT @DataCompressionType = CASE WHEN convert(varchar(200),SERVERPROPERTY ('edition')) LIKE 'Enterprise%' THEN @DataCompressionType ELSE 'NONE' END;

		-- if requested to drop the index, do that (if it exists)
		IF (@DropPK = 1)
		BEGIN
			IF EXISTS (SELECT IndexName FROM MD3.f_GetIndexes(@SchemaName,@TableName) WHERE IndexName = @PrimaryKeyName AND IsPrimaryKey = 1)
			BEGIN
				SET @SQL = 'ALTER TABLE ' + @SchemaName + '.' + @TableName + ' DROP CONSTRAINT '+ @PrimaryKeyName 
				EXEC MD3.ExecuteSQL @SQL;
				RETURN
			END
			ELSE 
			BEGIN
				--index was previously dropped, no work to do.  
				RETURN
			END
		END
	
    -- see if current PK properties are a match to the requested properties, but don't check PKName
    SELECT 
		@CurrentPKName = IndexName 
    FROM MD3.f_GetIndexes(@SchemaName,@TableName) 
    WHERE IsPrimaryKey = 1
      AND IsClustered = @IsClustered
      AND IndexKeys = @KeyColumns
	  AND FGorPartitionScheme = @FileGroupOrPartition
	  AND IdxFillFactor = @FillFactor
	  AND IsPadded = @PadIndex
	  AND CompressionSetting = @DataCompressionType
	  ;
 
	
	IF (@CurrentPKName =  @PrimaryKeyName)
    BEGIN
		--all properties and name are the same, no work to do RETURN
		RETURN;
	END
	ELSE
	IF @CurrentPKName IS NOT NULL  
	BEGIN
		SET @Msg = ' Primary key ' + @CurrentPKName + ' already exists with identical properties but with a different name.  Renaming... '
		
		EXEC MD3.InsertLogMessage
				@Message = @Msg, 
				@ProcedureName = 'MD3.CreatePrimaryKey', 
				@Severity = 'I';

		SELECT @SQL = 'sp_rename ''' + @SchemaName + '.' + @TableName + '.' + @CurrentPKName + ''',''' + @PrimaryKeyName + ''''
		EXEC MD3.ExecuteSQL @SQL;
		RETURN;
	END;
	;
	
	

    
    -- if we are here then we have a PK that is somehow different from what was requested.  
    SELECT @CurrentPKName = IndexName 
    FROM MD3.f_GetIndexes(@SchemaName,@TableName) 
    WHERE IsPrimaryKey = 1 


    IF (@CurrentPKName IS NOT NULL) 
    BEGIN

		--if table is replicated we need to remove it, but every environment is different, so for now just ERROR
		--even those this is far from ideal in a properties-based deployment
		IF (EXISTS (SELECT 1 FROM sys.tables WHERE is_replicated = 1 AND name = @TableName AND schema_id = SCHEMA_ID(@SchemaName) ))
		BEGIN
			SET @Msg = 'ERROR: Table ' + @TableName + ' is a replicated table, to alter PK we will need to drop replication first.'

			EXEC MD3.InsertLogMessage
				@Message = @Msg, 
				@ProcedureName = 'MD3.CreatePrimaryKey', 
				@Severity = 'E';
			RAISERROR (@Msg,16,1)
			RETURN 1
		END
		
		--remove any incoming FKs that are referencing this PK
		--FKs will be re-applied when FKs are run later during MD3 process
		--this proc will also give a visual Information cue to the developer to remember to do this if she
		--is not using MD3
		EXEC MD3.RemoveIncomingFKs
			@TableName = @TableName
			,@SchemaName = @SchemaName;
		
	   SET @Msg = 'PK: ' + @CurrentPKName + '  exists with different attributes' +
				   '  The PK will be DROP/CREATE. '
	   EXEC MD3.InsertLogMessage
			@Message = @Msg, 
			@ProcedureName = 'MD3.CreatePrimaryKey', 
			@Severity = 'I';

	   SET @SQL = 'ALTER TABLE ' + @SchemaName + '.' + @TableName + ' DROP CONSTRAINT '+ @CurrentPKName 
	   EXEC MD3.ExecuteSQL @SQL;
	 
    END
    


    --If @IsClustered = 1, check for existing clustered index and drop it.  Set a Message for developer to see to fix the corresponding index too.  
    IF (@IsClustered = 1)
    BEGIN
		IF EXISTS (SELECT * FROM MD3.f_GetIndexes(@SchemaName,@TableName) WHERE IsClustered = 1 AND IsPrimaryKey = 0)
		
		BEGIN
			DECLARE @IndexName SYSNAME
			SELECT @IndexName = IndexName FROM MD3.f_GetIndexes(@SchemaName,@TableName) WHERE IsClustered = 1
		
			SELECT @SQL = 'DROP INDEX ' + @IndexName + ' ON ' + @SchemaName + '.' + @TableName
			SET @Msg = 'Clustered Index: ' + @SchemaName + '.' + @TableName + '.' + @IndexName + ' has been dropped for a Primary Key Change, you may need to modify the related Index file'

		   EXEC MD3.InsertLogMessage
				@Message = @Msg, 
				@ProcedureName = 'MD3.CreatePrimaryKey', 
				@Severity = 'I';
		   EXEC MD3.ExecuteSQL @SQL;
		END
	END
    
    --Set the compression option
	SELECT @DataCompressionType = COALESCE(@DataCompressionType, 'NONE') ;

    -- string together the ALTER SQL
    SELECT @SQL = '' 
    SELECT @SQL = @SQL + 'SET QUOTED_IDENTIFIER ON; ALTER TABLE ' + @SchemaName + '.' + @TableName + ' ADD CONSTRAINT ' + @PrimaryKeyName + ' PRIMARY KEY'
    SELECT @SQL = @SQL + CASE WHEN @IsClustered = 1 THEN ' CLUSTERED' ELSE ' NONCLUSTERED' END
    SELECT @SQL = @SQL + ' (' + REPLACE(@KeyColumns,'(-)',' DESC') + ')'
    SELECT @SQL = @SQL +  CASE WHEN @FillFactor > 0 THEN (' WITH(FILLFACTOR = ' + CAST(@FillFactor AS VARCHAR(3)) +',')
							   ELSE ' WITH(' END
    SELECT @SQL = @SQL + CASE WHEN @PadIndex = 1 THEN ' PAD_INDEX = ON' ELSE ' PAD_INDEX = OFF' END
    SELECT @SQL = @SQL + ', SORT_IN_TEMPDB = ON'
    SELECT @SQL = @SQL + ', STATISTICS_NORECOMPUTE = OFF' -- Auto statistics turned on
    SELECT @SQL = @SQL + ', ALLOW_ROW_LOCKS = ON'
    SELECT @SQL = @SQL + ', ALLOW_PAGE_LOCKS = ON'
    SELECT @SQL = @SQL + ', MAXDOP = 8' 
    SELECT @SQL = @SQL + ', DATA_COMPRESSION = '+  @DataCompressionType
    SELECT @SQL = @SQL + ')'
    SELECT @SQL = @SQL + ' ON ' + CASE WHEN @FileGroupOrPartition = 'PRIMARY' THEN '[PRIMARY]' ELSE @FileGroupOrPartition END
    
		   EXEC MD3.InsertLogMessage
				@Message = 'Creating new PK definition with given parameters.', 
				@ProcedureName = 'MD3.CreatePrimaryKey', 
				@Severity = 'I';
		   EXEC MD3.ExecuteSQL @SQL;


    RETURN



END
GO
